package henu.service;

import henu.bean.*;
import henu.dao.factory.DaoFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.List;
import java.util.UUID;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.formula.functions.T;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.struts2.ServletActionContext;

/**
 * 
 * @author wuhaifeng 2015-1-15
 *
 */
public class ExportExcel {
	public static String ques(List<Questions> list,String basepath) {
		StringBuilder newxls = new StringBuilder() ; 
    	String modelpath =  basepath + "/upload/model/questionsmodel.xls" ;  
        InputStream in =null ;
        Workbook work  = null ;
		try {
			in = new FileInputStream(modelpath);
			work = new HSSFWorkbook(in);
		}catch (IOException e) {
			e.printStackTrace();
		}  
		Sheet sheet = work.getSheetAt(0);
		CellStyle cellstyle = sheet.getRow(0).getCell(0).getCellStyle(); //
		Row row = null;
		Cell cell = null;
		for (int i = 0; i < list.size(); i++) {
			Questions ques = list.get(i);
	    	
			row = sheet.createRow(i + 1); //


			cell = row.createCell(0);
			cell.setCellValue(ques.getContent());
			System.out.println("conteng:"+ques.getContent());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(1); 
			cell.setCellValue(ques.getAnswer());
			cell.setCellStyle(cellstyle);
			
			cell = row.createCell(2); 
			cell.setCellValue(ques.getSubject());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(3); 
			cell.setCellValue(ques.getType());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(4);
			cell.setCellValue(ques.getOptiona());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(5); 
			cell.setCellValue(ques.getOptionb());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(6);
			cell.setCellValue(ques.getOptionc());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(7); 
			cell.setCellValue(ques.getOptiond());
			cell.setCellStyle(cellstyle);
			
			
			cell = row.createCell(8); 
			cell.setCellValue(ques.getRemark());
			cell.setCellStyle(cellstyle);
		}
		newxls.append(basepath).append("/download/").append(RandomList.getNum(9999)).append(".xls");
		File  tempfile = new File(newxls.toString()); 
		//System.out.println("生成的文件的地址："+tempfile.toString());
		FileOutputStream os =null ;
		try {		
			os = new FileOutputStream(tempfile);
			work.write(os);
			os.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return newxls.toString();
	}
	
	public static String companyExce(List<Company> list,String basepath) {

		String newxls = "" ; 
    	String modelpath =  basepath + "/upload/model/CompanyMod.xls" ;  //获取模板文件
        InputStream in =null ;
        Workbook work  = null ;
		try {
			in = new FileInputStream(modelpath);
			System.out.println("模板文件地址："+modelpath) ;
			work = new HSSFWorkbook(in);
		}catch (IOException e) {
			System.out.println("打开模板文件出错");
			e.printStackTrace();
		}  
		
		// 得到第一个tab
		Sheet sheet = work.getSheetAt(0);
		CellStyle cellstyle = sheet.getRow(0).getCell(0).getCellStyle(); // 获取第一行第一个单元格的样式
		// 定义一行一列
		Row row = null;
		Cell cell = null;
		
		// 处理试题的导出
		for (int i = 0; i < list.size(); i++) {
			// 遍历取出来list中的数据
			Company c = list.get(i);
	    	
			row = sheet.createRow(i + 1); // 从第二行开始创建 单元格

			cell = row.createCell(0);
			cell.setCellValue(c.getComid());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(1); 
			cell.setCellValue(c.getBelongid());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(2); 
			cell.setCellValue(c.getCompanyname());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(3); 
			cell.setCellValue(c.getCompanytype());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(4); // 学生缴费
			cell.setCellValue(c.getAddress());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(5); // 备注
			cell.setCellValue(c.getLegalperson());
			cell.setCellStyle(cellstyle);
			
			
			cell = row.createCell(6); // 是否必收
			cell.setCellValue(c.getMemo1());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(7); // 是否必收
			cell.setCellValue(c.getBusinesslicense());
			cell.setCellStyle(cellstyle);
			
			cell = row.createCell(8); // 是否必收
			cell.setCellValue(c.getOrganizationcode());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(9); // 是否必收
			cell.setCellValue(c.getContactphone());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(10);
			cell.setCellValue(c.getIsexistcompany());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(11); 
			cell.setCellValue(c.getCreatetime());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(12); // 是否必收
			cell.setCellValue(c.getBusinessrange());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(13); // 是否必收
			cell.setCellValue(c.getOrganizecard());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(14); // 是否必收
			cell.setCellValue(c.getContactaddress());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(15); // 是否必收
			cell.setCellValue(c.getRegistmoney());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(16); // 是否必收
			cell.setCellValue(c.getRegisteroffice());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(17); // 是否必收
			cell.setCellValue(c.getBusinesslimit());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(18); // 是否必收
			cell.setCellValue(c.getAllowbusinesscard());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(19); // 是否必收
			cell.setCellValue(c.getPostcode());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(20); // 是否必收
			cell.setCellValue(c.getBasicaccoutopen());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(21); // 是否必收
			cell.setCellValue(c.getBasicaccoutid());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(22); // 是否必收
			cell.setCellValue(c.getCompanyprincipal());
			cell.setCellStyle(cellstyle);
			


			


			cell = row.createCell(23); // 是否必收
			cell.setCellValue(c.getMemo2());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(24); // 是否必收
			cell.setCellValue(c.getMemo3());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(25); // 是否必收
			cell.setCellValue(c.getMemo4());
			cell.setCellStyle(cellstyle);
			
		}
		newxls = basepath + "/download/" + UUID.randomUUID().toString()
				+ ".xls";
		System.out.println("newxls生成的文件下载路径:"+newxls);
		// 此处要处理错误 
		FileOutputStream os =null ;
		try {
			os = new FileOutputStream(newxls);
			work.write(os);
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return newxls;
	}

	public static String CarEx(List<Car> list,String basepath) {

		String newxls = "" ; 
    	String modelpath =  basepath + "/upload/model/CarMod.xls" ;  //获取模板文件
        InputStream in =null ;
        Workbook work  = null ;
		try {
			in = new FileInputStream(modelpath);
			System.out.println("模板文件地址："+modelpath) ;
			work = new HSSFWorkbook(in);
		}catch (IOException e) {
			System.out.println("打开模板文件出错");
			e.printStackTrace();
		}  
		
		// 得到第一个tab
		Sheet sheet = work.getSheetAt(0);
		CellStyle cellstyle = sheet.getRow(0).getCell(0).getCellStyle(); // 获取第一行第一个单元格的样式
		// 定义一行一列
		Row row = null;
		Cell cell = null;
		
		// 处理试题的导出
		for (int i = 0; i < list.size(); i++) {
			// 遍历取出来list中的数据
			Car c = list.get(i);
	    	
			row = sheet.createRow(i + 1); // 从第二行开始创建 单元格

			cell = row.createCell(0);
			cell.setCellValue(c.getVehicleLicense());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(1); 
			cell.setCellValue(c.getComid());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(2); 
			cell.setCellValue(c.getVin());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(3); 
			cell.setCellValue(c.getUserid());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(4); // 学生缴费
			cell.setCellValue(c.getVehicleType());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(5); // 备注
			cell.setCellValue(c.getBuyTime());
			cell.setCellStyle(cellstyle);
			
			
			cell = row.createCell(6); // 是否必收
			cell.setCellValue(c.getCreateTime());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(7); // 是否必收
			cell.setCellValue(c.getIsTrailer());
			cell.setCellStyle(cellstyle);
			
			cell = row.createCell(8); // 是否必收
			cell.setCellValue(c.getStatus());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(9); // 是否必收
			cell.setCellValue(c.getEngineno());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(10);
			cell.setCellValue(c.getVfn());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(11); 
			cell.setCellValue(c.getCarLength());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(12); // 是否必收
			cell.setCellValue(c.getCarWidth());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(13); // 是否必收
			cell.setCellValue(c.getCarHeight());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(14); // 是否必收
			cell.setCellValue(c.getDeadWeight());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(15); // 是否必收
			cell.setCellValue(c.getHorsePower());
			cell.setCellStyle(cellstyle);
			


			cell = row.createCell(16); // 是否必收
			cell.setCellValue(c.getQrCode());
			cell.setCellStyle(cellstyle);
			
;
			
		}
		newxls = basepath + "/download/" + UUID.randomUUID().toString()
				+ ".xls";
		System.out.println("newxls生成的文件下载路径:"+newxls);
		// 此处要处理错误 
		FileOutputStream os =null ;
		try {
			os = new FileOutputStream(newxls);
			work.write(os);
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return newxls;
	}
	public static String ContentEx(List<Content> list,String basepath) {

		String newxls = "" ; 
    	String modelpath =  basepath + "/upload/model/ContentMod.xls" ;  //获取模板文件
        InputStream in =null ;
        Workbook work  = null ;
		try {
			in = new FileInputStream(modelpath);
			System.out.println("模板文件地址："+modelpath);
			work = new HSSFWorkbook(in);
		}catch (IOException e) {
			System.out.println("打开模板文件出错");
			e.printStackTrace();
		}  
		
		// 得到第一个tab
		Sheet sheet = work.getSheetAt(0);
		CellStyle cellstyle = sheet.getRow(0).getCell(0).getCellStyle(); // 获取第一行第一个单元格的样式
		// 定义一行一列
		Row row = null;
		Cell cell = null;
		
		// 处理试题的导出
		for (int i = 0; i < list.size(); i++) {
			// 遍历取出来list中的数据
			Content s = list.get(i);
	    	
			row = sheet.createRow(i + 1); // 从第二行开始创建 单元格

			cell = row.createCell(0);
			cell.setCellValue(s.getTitle());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(1); 
			cell.setCellValue(s.getContent());
			cell.setCellStyle(cellstyle);


			cell = row.createCell(2); 
			cell.setCellValue(s.getEdutype());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(3); 
			cell.setCellValue(s.getPublishtime());
			cell.setCellStyle(cellstyle);
			

			cell = row.createCell(4);
			cell.setCellValue(s.getPublisher());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(5); 
			cell.setCellValue(s.getViewnumbers());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(6); 
			cell.setCellValue(s.getMemo());
			cell.setCellStyle(cellstyle);
			
			
			cell = row.createCell(7); 
			cell.setCellValue(s.getSeid());
			cell.setCellStyle(cellstyle);
			



			
		}
		newxls = basepath + "/download/" + UUID.randomUUID().toString()
				+ ".xls";
		System.out.println("newxls生成的文件下载路径:"+newxls);
		// 此处要处理错误 
		FileOutputStream os =null ;
		try {
			os = new FileOutputStream(newxls);
			work.write(os);
			os.close();
		} catch (IOException e) {
			e.printStackTrace();
		}
		return newxls;
	}
	
	public static String UserVEx(List<UserVehicle> list,String basepath) {
		StringBuilder newxls = new StringBuilder() ; 
    	String modelpath =  basepath + "/upload/model/UserVMod.xls" ;  
        InputStream in =null ;
        Workbook work  = null ;
		try {
			in = new FileInputStream(modelpath);
			work = new HSSFWorkbook(in);
		}catch (IOException e) {
			e.printStackTrace();
		}  
		Sheet sheet = work.getSheetAt(0);
		CellStyle cellstyle = sheet.getRow(0).getCell(0).getCellStyle(); //
		Row row = null;
		Cell cell = null;
		for (int i = 0; i < list.size(); i++) {
			UserVehicle	 uv = list.get(i);
	    	
			row = sheet.createRow(i + 1); //

			cell = row.createCell(0); 
			cell.setCellValue(uv.getUvid());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(1);
			cell.setCellValue(uv.getIdcard());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(2); 
			cell.setCellValue(uv.getCarid());
			cell.setCellStyle(cellstyle);

			cell = row.createCell(3); 
			cell.setCellValue(uv.getMemo());
			cell.setCellStyle(cellstyle);

		}
		newxls.append(basepath).append("/download/").append(RandomList.getNum(9999)).append(".xls");
		File  tempfile = new File(newxls.toString()); 
		//System.out.println("生成的文件的地址："+tempfile.toString());
		FileOutputStream os =null ;
		try {		
			os = new FileOutputStream(tempfile);
			work.write(os);
			os.flush();
		} catch (IOException e) {
			e.printStackTrace();
		}finally{
			try {
				os.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return newxls.toString();
	}
	
}
